using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using XBLib.Dal.AdoDal;
using DataInfo;
using KenLib.Common;
namespace KenLib.Dal
{
    public class 学歴情報Dao : BaseAdoDao
    {
        private string m_requestTime;
        private string m_displayTime;
        public int Insert(学歴情報 obj)
        {
            int i = 0;
            try
            {
                m_requestTime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
                List<DbParameter> ls = new GlobalDao().CreateArrayParam(obj, "廃止区分,開始時期,学校住所,学校名,学歴区分,社員ID,終了時期,専攻学科,専攻学部,専攻区分,専攻系列コード,専攻内容,専門技術,全日夜間,区分,専攻系列,担当教授名");
                DbCommand command = _daoHelper.CreateCommand(CommandNameManager.Insert_TraineeAcademicInfo, CommandType.StoredProcedure, ls.ToArray());
                i = _daoHelper.Execute(command);
                m_displayTime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
                ExportFile.WriteLog("", "", "INSERT", "Insert_TraineeAcademicInfo", m_requestTime, m_displayTime);
            }
            catch (Exception ex)
            {
                m_displayTime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
                ExportFile.WriteErr("", "", "SELECT", ex.Message, m_requestTime, m_displayTime);
            }
            return i;
        }

        public int Update(学歴情報 obj)
        {
            int i = 0;
            try
            {
                m_requestTime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
                List<DbParameter> ls = new GlobalDao().CreateArrayParam(obj, "ID,廃止区分,開始時期,学校住所,学校名,学歴区分,社員ID,終了時期,専攻学科,専攻学部,専攻区分,専攻系列コード,専攻内容,専門技術,全日夜間,区分,専攻系列,担当教授名");

                DbCommand command = _daoHelper.CreateCommand(CommandNameManager.Update_TraineeAcademicInfo, CommandType.StoredProcedure, ls.ToArray());
                i = _daoHelper.Execute(command);
                m_displayTime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
                ExportFile.WriteLog("", "", "UPDATE", "Update_TraineeAcademicInfo", m_requestTime, m_displayTime);
            }
            catch (Exception ex)
            {
                m_displayTime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
                ExportFile.WriteErr("", "", "SELECT", ex.Message, m_requestTime, m_displayTime);
            }
            return i;
        }

        public int Delete(int id)
        {
            int i = 0;
            try
            {
                m_requestTime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
                DbParameter param0 = _daoHelper.CreateParameter("@ID", id, DbType.Int32);

                DbCommand command = _daoHelper.CreateCommand(CommandNameManager.Delete_TraineeAcademicInfo, CommandType.StoredProcedure, param0);
                i = _daoHelper.Execute(command);
                m_displayTime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
                ExportFile.WriteLog("", "", "DELETE", "Delete_TraineeAcademicInfo", m_requestTime, m_displayTime);
            }
            catch (Exception ex)
            {
                m_displayTime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
                ExportFile.WriteErr("", "", "SELECT", ex.Message, m_requestTime, m_displayTime);
            }
            return i;
        }

        public int InsertPartial(学歴情報 obj)
        {
            int i = 0;
            try
            {
                DbCommand commands = _daoHelper.CreateCommand(CommandNameManager.sp_Insert学歴情報_Partial, CommandType.StoredProcedure);
                DbParameter para1 = _daoHelper.CreateParameter("@廃止区分", obj.廃止区分);
                DbParameter para2 = _daoHelper.CreateParameter("@区分", obj.区分);
                DbParameter para3 = _daoHelper.CreateParameter("@学歴区分", obj.学歴区分);
                DbParameter para4 = _daoHelper.CreateParameter("@学校名", obj.学校名);
                DbParameter para5 = _daoHelper.CreateParameter("@学校住所", obj.学校住所);
                DbParameter para6 = _daoHelper.CreateParameter("@専攻区分", obj.専攻区分);
                DbParameter para7 = _daoHelper.CreateParameter("@専攻系列コード", obj.専攻系列コード_1);
                DbParameter para8 = _daoHelper.CreateParameter("@専攻学部", obj.専攻学部);
                DbParameter para9 = _daoHelper.CreateParameter("@専攻学科", obj.専攻学科);
                DbParameter para10 = _daoHelper.CreateParameter("@専攻内容", obj.専攻内容);
                DbParameter para11 = _daoHelper.CreateParameter("@専門技術", obj.専門技術);
                DbParameter para12 = _daoHelper.CreateParameter("@担当教授名", obj.担当教授名);
                DbParameter para13 = _daoHelper.CreateParameter("@開始時期", obj.開始時期);
                DbParameter para14 = _daoHelper.CreateParameter("@終了時期", obj.終了時期);
                DbParameter para15 = _daoHelper.CreateParameter("@社員ID", obj.社員ID);

                commands.Parameters.Add(para1);
                commands.Parameters.Add(para2);
                commands.Parameters.Add(para3);
                commands.Parameters.Add(para4);
                commands.Parameters.Add(para5);
                commands.Parameters.Add(para6);
                commands.Parameters.Add(para7);
                commands.Parameters.Add(para8);
                commands.Parameters.Add(para9);
                commands.Parameters.Add(para10);
                commands.Parameters.Add(para11);
                commands.Parameters.Add(para12);
                commands.Parameters.Add(para13);
                commands.Parameters.Add(para14);
                commands.Parameters.Add(para15);

                i = _daoHelper.Execute(commands);
            }
            catch (Exception ex) { }

            return i;
        }

        public List<学歴情報> SelectPartial(String 社員ID)
        {
            try
            {

                DbCommand commands = _daoHelper.CreateCommand(CommandNameManager.sp_Select学歴情報_Partial, CommandType.StoredProcedure);
                DbParameter para = _daoHelper.CreateParameter("@社員ID", 社員ID);
                commands.Parameters.Add(para);

                return _businessObjectHelper.FillToList<学歴情報>(_daoHelper.Query(commands));
            }
            catch (Exception exx)
            {
                throw new Exception(exx.Message);
            }
        }
        #region TUNG
        public DataTable getGrv_学歴(int 社員ID)
        {
            DbParameter dbParam1 = _daoHelper.CreateParameter("@社員ID", 社員ID, DbType.Int32);
            DbCommand dbcommand = _daoHelper.CreateCommand("sp_getGrv_学歴_TUNG", CommandType.StoredProcedure, dbParam1);
            return _daoHelper.Query(dbcommand);
        }
        #endregion


        #region Vh
        /// <summary>
        /// Insert data into table 学歴情報
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataTable select_dataForModeInsert_55_Vh(String id)
        {
            DbCommand com = _daoHelper.CreateCommand("select_dataForModeInsert_55_Vh", CommandType.StoredProcedure);
            DbParameter para = _daoHelper.CreateParameter("@社員ID", id);
            com.Parameters.Add(para);
            return _daoHelper.Query(com);
        }

        public int insert_dataForModeInsert_55_Vh(学歴情報 obj)
        {
            int i = 0;
            DbCommand com = _daoHelper.CreateCommand("insert_dataForModeInsert_55_Vh", CommandType.StoredProcedure);
            DbParameter para1 = _daoHelper.CreateParameter("@区分", obj.区分);
            DbParameter para2 = _daoHelper.CreateParameter("@学歴区分", obj.学歴区分);
            DbParameter para3 = _daoHelper.CreateParameter("@学校名", obj.学校名);
            DbParameter para4 = _daoHelper.CreateParameter("@学校住所", obj.学校住所);
            DbParameter para5 = _daoHelper.CreateParameter("@専攻区分", obj.専攻区分);
            DbParameter para6 = _daoHelper.CreateParameter("@専攻系列コード", obj.専攻系列コード_1);
            DbParameter para7 = _daoHelper.CreateParameter("@専攻学部", obj.専攻学部);
            DbParameter para8 = _daoHelper.CreateParameter("@専攻学科", obj.専攻学科);
            DbParameter para9 = _daoHelper.CreateParameter("@専攻内容", obj.専攻内容);
            DbParameter para10 = _daoHelper.CreateParameter("@専門技術", obj.専門技術);
            DbParameter para11 = _daoHelper.CreateParameter("@担当教授名", obj.担当教授名);
            DbParameter para12 = _daoHelper.CreateParameter("@開始時期", obj.開始時期);
            DbParameter para12_1 = _daoHelper.CreateParameter("@終了時期", obj.終了時期);
            DbParameter para13 = _daoHelper.CreateParameter("@社員ID", obj.社員ID);
            

            com.Parameters.Add(para1);
            com.Parameters.Add(para2);
            com.Parameters.Add(para3);
            com.Parameters.Add(para4);
            com.Parameters.Add(para5);
            com.Parameters.Add(para6);
            com.Parameters.Add(para7);
            com.Parameters.Add(para8);
            com.Parameters.Add(para9);
            com.Parameters.Add(para10);
            com.Parameters.Add(para11);
            com.Parameters.Add(para12);
            com.Parameters.Add(para12_1);
            com.Parameters.Add(para13);
            
            i = _daoHelper.Execute(com);
            return i;
        }

        public DataTable select_dataForModeUpdate_55_Vh(String id)
        {
            DbCommand com = _daoHelper.CreateCommand("select_dataForModeUpdate_55_Vh", CommandType.StoredProcedure);
            DbParameter para = _daoHelper.CreateParameter("@ID", id);
            com.Parameters.Add(para);
            return _daoHelper.Query(com);
        }

        public int update_dataForModeUpdate_55_Vh(学歴情報 obj)
        {
            int i = 0;
            DbCommand com = _daoHelper.CreateCommand("update_dataForModeUpdate_55_Vh", CommandType.StoredProcedure);
            DbParameter para1 = _daoHelper.CreateParameter("@区分", obj.区分);
            DbParameter para2 = _daoHelper.CreateParameter("@学歴区分", obj.学歴区分);
            DbParameter para3 = _daoHelper.CreateParameter("@学校名", obj.学校名);
            DbParameter para4 = _daoHelper.CreateParameter("@学校住所", obj.学校住所);
            DbParameter para5 = _daoHelper.CreateParameter("@専攻区分", obj.専攻区分);
            DbParameter para6 = _daoHelper.CreateParameter("@専攻系列コード", obj.専攻系列コード_1);
            DbParameter para7 = _daoHelper.CreateParameter("@専攻学部", obj.専攻学部);
            DbParameter para8 = _daoHelper.CreateParameter("@専攻学科", obj.専攻学科);
            DbParameter para9 = _daoHelper.CreateParameter("@専攻内容", obj.専攻内容);
            DbParameter para10 = _daoHelper.CreateParameter("@専門技術", obj.専門技術);
            DbParameter para11 = _daoHelper.CreateParameter("@担当教授名", obj.担当教授名);
            DbParameter para12 = _daoHelper.CreateParameter("@開始時期", obj.開始時期);
            DbParameter para12_1 = _daoHelper.CreateParameter("@終了時期", obj.終了時期);
            DbParameter para13 = _daoHelper.CreateParameter("@ID", obj.ID);


            com.Parameters.Add(para1);
            com.Parameters.Add(para2);
            com.Parameters.Add(para3);
            com.Parameters.Add(para4);
            com.Parameters.Add(para5);
            com.Parameters.Add(para6);
            com.Parameters.Add(para7);
            com.Parameters.Add(para8);
            com.Parameters.Add(para9);
            com.Parameters.Add(para10);
            com.Parameters.Add(para11);
            com.Parameters.Add(para12);
            com.Parameters.Add(para12_1);
            com.Parameters.Add(para13);

            i = _daoHelper.Execute(com);
            return i;
        }

        public int insert_dataForModeInsert_55_Vh(学歴情報 obj, out int index)
        {
            int i = 0;
            DbCommand com = _daoHelper.CreateCommand("insert_dataForModeInsert_js55_Vh", CommandType.StoredProcedure);
            DbParameter para1 = _daoHelper.CreateParameter("@区分", obj.区分);
            DbParameter para2 = _daoHelper.CreateParameter("@学歴区分", obj.学歴区分);
            DbParameter para3 = _daoHelper.CreateParameter("@学校名", obj.学校名);
            DbParameter para4 = _daoHelper.CreateParameter("@学校住所", obj.学校住所);
            DbParameter para5 = _daoHelper.CreateParameter("@専攻区分", obj.専攻区分);
            DbParameter para6 = _daoHelper.CreateParameter("@専攻系列コード", obj.専攻系列コード_1);
            DbParameter para7 = _daoHelper.CreateParameter("@専攻学部", obj.専攻学部);
            DbParameter para8 = _daoHelper.CreateParameter("@専攻学科", obj.専攻学科);
            DbParameter para9 = _daoHelper.CreateParameter("@専攻内容", obj.専攻内容);
            DbParameter para10 = _daoHelper.CreateParameter("@専門技術", obj.専門技術);
            DbParameter para11 = _daoHelper.CreateParameter("@担当教授名", obj.担当教授名);
            DbParameter para12 = _daoHelper.CreateParameter("@開始時期", obj.開始時期);
            DbParameter para12_1 = _daoHelper.CreateParameter("@終了時期", obj.終了時期);
            DbParameter para13 = _daoHelper.CreateParameter("@社員ID", obj.社員ID);
            DbParameter para14 = _daoHelper.CreateParameter("@IdCurrent", obj.ID);
            para14.Direction = ParameterDirection.Output;

            com.Parameters.Add(para1);
            com.Parameters.Add(para2);
            com.Parameters.Add(para3);
            com.Parameters.Add(para4);
            com.Parameters.Add(para5);
            com.Parameters.Add(para6);
            com.Parameters.Add(para7);
            com.Parameters.Add(para8);
            com.Parameters.Add(para9);
            com.Parameters.Add(para10);
            com.Parameters.Add(para11);
            com.Parameters.Add(para12);
            com.Parameters.Add(para12_1);
            com.Parameters.Add(para13);
            com.Parameters.Add(para14);

            i = _daoHelper.Execute(com);
            index = (Int32)com.Parameters["@IdCurrent"].Value;
            return i;
        }

        public DataTable SelectPartialTable(string 社員ID)
        {
            try
            {

                DbCommand commands = _daoHelper.CreateCommand(CommandNameManager.sp_Select学歴情報_Partial, CommandType.StoredProcedure);
                DbParameter para = _daoHelper.CreateParameter("@社員ID", 社員ID);
                commands.Parameters.Add(para);
                DataTable dt;

                dt = _daoHelper.Query(commands);
                return dt;
            }
            catch (Exception exx)
            {
                throw new Exception(exx.Message);
            }
        }

        public int InsertPartialTable(学歴情報 obj, out int ID)
        {
            int i = 0;

            ID = 0;
            DbCommand commands = _daoHelper.CreateCommand("sp_Insert学歴情報_Partial_52_Hoat_2", CommandType.StoredProcedure);
            DbParameter para1 = _daoHelper.CreateParameter("@廃止区分", obj.廃止区分);
            DbParameter para2 = _daoHelper.CreateParameter("@区分", obj.区分);
            DbParameter para3 = _daoHelper.CreateParameter("@学歴区分", obj.学歴区分);
            DbParameter para4 = _daoHelper.CreateParameter("@学校名", obj.学校名);
            DbParameter para5 = _daoHelper.CreateParameter("@学校住所", obj.学校住所);
            DbParameter para6 = _daoHelper.CreateParameter("@専攻区分", obj.専攻区分);
            DbParameter para7 = _daoHelper.CreateParameter("@専攻系列コード", obj.専攻系列コード_1);
            DbParameter para8 = _daoHelper.CreateParameter("@専攻学部", obj.専攻学部);
            DbParameter para9 = _daoHelper.CreateParameter("@専攻学科", obj.専攻学科);
            DbParameter para10 = _daoHelper.CreateParameter("@専攻内容", obj.専攻内容);
            DbParameter para11 = _daoHelper.CreateParameter("@専門技術", obj.専門技術);
            DbParameter para12 = _daoHelper.CreateParameter("@担当教授名", obj.担当教授名);
            DbParameter para13 = _daoHelper.CreateParameter("@開始時期", obj.開始時期);
            DbParameter para14 = _daoHelper.CreateParameter("@終了時期", obj.終了時期);
            DbParameter para15 = _daoHelper.CreateParameter("@社員ID", obj.社員ID);
            DbParameter para16 = _daoHelper.CreateParameter("@ID", 0);
            para16.Direction = ParameterDirection.Output;

            commands.Parameters.Add(para1);
            commands.Parameters.Add(para2);
            commands.Parameters.Add(para3);
            commands.Parameters.Add(para4);
            commands.Parameters.Add(para5);
            commands.Parameters.Add(para6);
            commands.Parameters.Add(para7);
            commands.Parameters.Add(para8);
            commands.Parameters.Add(para9);
            commands.Parameters.Add(para10);
            commands.Parameters.Add(para11);
            commands.Parameters.Add(para12);
            commands.Parameters.Add(para13);
            commands.Parameters.Add(para14);
            commands.Parameters.Add(para15);
            commands.Parameters.Add(para16);

            i = _daoHelper.Execute(commands);
            ID = Int32.Parse(commands.Parameters["@ID"].Value.ToString());

            return i;
        }

        public int UpdatePartialTable(学歴情報 obj, int IdCurrent)
        {
            int i = 0;

            DbCommand commands = _daoHelper.CreateCommand("sp_Update学歴情報_Partial_52_Hoat", CommandType.StoredProcedure);
            DbParameter para1 = _daoHelper.CreateParameter("@廃止区分", obj.廃止区分);
            DbParameter para2 = _daoHelper.CreateParameter("@区分", obj.区分);
            DbParameter para3 = _daoHelper.CreateParameter("@学歴区分", obj.学歴区分);
            DbParameter para4 = _daoHelper.CreateParameter("@学校名", obj.学校名);
            DbParameter para5 = _daoHelper.CreateParameter("@学校住所", obj.学校住所);
            DbParameter para6 = _daoHelper.CreateParameter("@専攻区分", obj.専攻区分);
            DbParameter para7 = _daoHelper.CreateParameter("@専攻系列コード", obj.専攻系列コード_1);
            DbParameter para8 = _daoHelper.CreateParameter("@専攻学部", obj.専攻学部);
            DbParameter para9 = _daoHelper.CreateParameter("@専攻学科", obj.専攻学科);
            DbParameter para10 = _daoHelper.CreateParameter("@専攻内容", obj.専攻内容);
            DbParameter para11 = _daoHelper.CreateParameter("@専門技術", obj.専門技術);
            DbParameter para12 = _daoHelper.CreateParameter("@担当教授名", obj.担当教授名);
            DbParameter para13 = _daoHelper.CreateParameter("@開始時期", obj.開始時期);
            DbParameter para14 = _daoHelper.CreateParameter("@終了時期", obj.終了時期);
            DbParameter para16 = _daoHelper.CreateParameter("@ID", IdCurrent);
            

            commands.Parameters.Add(para1);
            commands.Parameters.Add(para2);
            commands.Parameters.Add(para3);
            commands.Parameters.Add(para4);
            commands.Parameters.Add(para5);
            commands.Parameters.Add(para6);
            commands.Parameters.Add(para7);
            commands.Parameters.Add(para8);
            commands.Parameters.Add(para9);
            commands.Parameters.Add(para10);
            commands.Parameters.Add(para11);
            commands.Parameters.Add(para12);
            commands.Parameters.Add(para13);
            commands.Parameters.Add(para14);
            commands.Parameters.Add(para16);

            i = _daoHelper.Execute(commands);


            return i;
        }

        /// <summary>
        /// Coder: Ha Van Hoat
        /// Date: 05.03.2012
        /// Function: check exists in table 学歴情報
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public int checkExists学歴情報_InsertMode_JS55(学歴情報 obj)
        {
            int i = 0;
            DbCommand com = _daoHelper.CreateCommand("checkExists学歴情報_InsertMode_JS55_Vh", CommandType.StoredProcedure);
            
            DbParameter para2 = _daoHelper.CreateParameter("@学歴区分", obj.学歴区分);
            DbParameter para3 = _daoHelper.CreateParameter("@学校名", obj.学校名);
            DbParameter para12 = _daoHelper.CreateParameter("@開始時期", obj.開始時期);
            DbParameter para12_1 = _daoHelper.CreateParameter("@終了時期", obj.終了時期);
            DbParameter para13 = _daoHelper.CreateParameter("@社員ID", obj.社員ID);
            
            com.Parameters.Add(para2);
            com.Parameters.Add(para3);
            com.Parameters.Add(para12);
            com.Parameters.Add(para12_1);
            com.Parameters.Add(para13);

            DataTable dt = _daoHelper.Query(com);
            if (dt.Rows.Count > 0)
                i = 1;//exists

            return i;
        }

        /// <summary>
        ///     Check had changing data. 
        /// </summary>
        /// <param name="obj"></param>
        /// <returns>true: if had changed</returns>
        public bool checkHasChangedData(学歴情報 obj)
        {
            bool flag = true;// Had changed
            DbCommand com = _daoHelper.CreateCommand("checkHasChangedData学歴情報_UpdateCurrent_JS55_Vh", CommandType.StoredProcedure);

            DbParameter para2 = _daoHelper.CreateParameter("@学歴区分", obj.学歴区分);
            DbParameter para3 = _daoHelper.CreateParameter("@学校名", obj.学校名);
            DbParameter para12 = _daoHelper.CreateParameter("@開始時期", obj.開始時期);
            DbParameter para12_1 = _daoHelper.CreateParameter("@終了時期", obj.終了時期);
            DbParameter para13 = _daoHelper.CreateParameter("@ID", obj.ID);

            com.Parameters.Add(para2);
            com.Parameters.Add(para3);
            com.Parameters.Add(para12);
            com.Parameters.Add(para12_1);
            com.Parameters.Add(para13);

            DataTable dt = _daoHelper.Query(com);
            if (dt.Rows.Count > 0)
                flag = false; // not change
            return flag;
        }

        #endregion
    }
}
